#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import MySQLdb
import re
import sys

# Nombre de lignes avant flush du fichier

flush_limit = 5000

## Connection a la base de donnees

db = MySQLdb.connect(db = "megabase", user='root', passwd = "OhGheiw8EichahGh")

## Recherche des differentes listes disponibles

cur = db.cursor()
cur.execute("SELECT `id_cat`,`nom_cat` FROM `categories` ORDER BY `id_cat` ASC")
round = cur.fetchone()

listes = []

while (round != None):
    listes.append((round[0], round[1]))
    round = cur.fetchone()

cur.close()

## Initialisation du compteur de listes

nb_listes = 0

## Parcours de la liste

for i in listes:

    ## Initialisation des compteurs
    
    nb_cli_neuf = 0
    nb_cli_orange = 0
    nb_cli_sfr = 0
    nb_cli_wanadoo = 0
    nb_cli = 0

    ## Generation des noms de fichier

    basename = str(listes[nb_listes][0]) + '-' + listes[nb_listes][1]

    ## Boucle

    ## Extraction des donnees

    cur = db.cursor()
    print('\n---------------------------------------------\n')
    print('Extraction des donnes de ' + re.sub(' ', '_', listes[nb_listes][1]))

    cur.execute("SELECT `id`,`adresse` FROM `adresses` WHERE `categorie`=" + str(listes[nb_listes][0]) + " AND `bounced`=0 ORDER BY `adresse` ASC")

    ## Premiere iteration

    round = cur.fetchone()

    ## Boucle de lecture des resultats

    print('Lecture et traitement des resultats')

    while round != None:

        ## Lecture de l'adresse

        current_email = round[1]

        ## Separation du nom et du domaine

        current_prefix = current_email.split('@')[0]
        current_suffix = current_email.split('@')[1]

        if current_suffix == "neuf.fr":
            nb_cli_neuf = nb_cli_neuf + 1

        elif current_suffix == "orange.fr":
            nb_cli_orange = nb_cli_orange + 1

        elif current_suffix == "sfr.fr":
            nb_cli_sfr = nb_cli_sfr + 1

        elif current_suffix == "wanadoo.fr":
            nb_cli_wanadoo = nb_cli_wanadoo + 1

        else:
            nb_cli = nb_cli + 1

        ## Ouverture du fichier si besoin

        if current_suffix == "neuf.fr" and nb_cli_neuf == 1:
            fileNeuf = open(basename + '_neuf.csv', 'w')

        elif current_suffix == "orange.fr" and nb_cli_orange == 1:
            fileOrange = open(basename + '_orange.csv', 'w')

        elif current_suffix == "sfr.fr" and nb_cli_sfr == 1:
            fileSfr = open(basename + '_sfr.csv', 'w')

        elif current_suffix == "wanadoo.fr" and nb_cli_wanadoo == 1:
            fileWanadoo = open(basename + '_wanadoo.csv', 'w')

        elif nb_cli == 1:
            file = open(basename + '.csv', 'w')

        ## Ecriture de l'adresse et de l'entete si besoin

        ######### email marketer #########
        #if nb_subscribers == 1:
        #    file.write('"Email Address","Email Format","Confirmed","Subscribe Date (mm/dd/yyyy)"\n')
        #file.write('"' + current_email + '","HTML","Confirmed","09/26/2009"\n')
        ##################################

        ############ phplist #############
        #if (nb_subscribers == 1):
        #    file.write('email,\n')
        #file.write(current_email + ',\n')
        ##################################

        ############ indiens #############
        #file.write(current_email + '\n')
        ##################################

        ############# voloMP #############
        if current_suffix == "neuf.fr":
            fileNeuf.write(current_email + ',\n')
        elif current_suffix == "orange.fr":
            fileOrange.write(current_email + ',\n')
        elif current_suffix == "sfr.fr":
            fileSfr.write(current_email + ',\n')
        elif current_suffix == "wanadoo.fr":
            fileWanadoo.write(current_email + ',\n')
        else:
            file.write(current_email + ',\n')
        ##################################

        ## Lecture des compteurs de flush

        if current_suffix == 'neuf.fr' and ( str(nb_cli_neuf)[-4:] == "5000" or str(nb_cli_neuf)[-4:] == "0000" ):
            fileNeuf.flush()
        elif current_suffix == 'orange.fr' and ( str(nb_cli_orange)[-4:] == "5000" or str(nb_cli_orange)[-4:] == "0000" ):
            fileOrange.flush()
        elif current_suffix == 'sfr.fr' and ( str(nb_cli_sfr)[-4:] == "5000" or str(nb_cli_sfr)[-4:] == "0000" ):
            fileSfr.flush()
        elif current_suffix == 'wanadoo.fr' and ( str(nb_cli_wanadoo)[-4:] == "5000" or str(nb_cli_wanadoo)[-4:] == "0000" ):
            fileWanadoo.flush()
        elif str(nb_cli)[-4:] == "5000" or str(nb_cli)[-4:] == "0000":
            file.flush()

        ## Iteration

        round = cur.fetchone()

    ## Fermeture du curseur d'extraction

    cur.close()

    ## Fermeture du fichier
    if nb_cli != 0:
        file.close()
    if nb_cli_neuf != 0:
        fileNeuf.close()
    if nb_cli_orange != 0:
        fileOrange.close()
    if nb_cli_sfr != 0:
        fileSfr.close()
    if nb_cli_wanadoo != 0:
        fileWanadoo.close()

    ## Incrementation du compteur de listes

    nb_listes = nb_listes + 1

## Fin du script

print('\n---------------------------------------------\n')
print('\nFini !!\n\n')

sys.exit(0)
